<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("player_profile", $con);

//$teamOrCollege = $_GET["team_or_college"];
$teamOrCollege = 'college';
$type = 'college';

$duplicates = array();
foreach (glob("*.csv") as $filename) {
	echo "$filename<br>";
	$handle = fopen($filename, "r");
	$first = true;
	if ($handle) {
		if($first){
			$title = fgets($handle, 4096);
			$first = false;
		}
		//echo "$award_title Winners:<br>";
		while (($buffer = fgets($handle, 4096)) !== false) {
			$line = explode(',', $buffer);
			$year = trim($line[0]);
			//echo "$year: ";
			$name = trim($line[1]);
			$parts = explode(' ', $name);
			if(sizeof($parts) == 2){
				$first = $parts[0];
				$last = $parts[1];
				$middle = null;
			}
			else if(sizeof($parts) == 3){
				$first = $parts[0];
				$middle = $parts[1];
				$last = $parts[2];
			}
			$first = preg_replace("/\"/", "", $first);
			$first = preg_replace("/'/", "\'", $first);
			$last = preg_replace("/\"/", "", $last);
			$last = preg_replace("/'/", "\'", $last);
			$college = str_replace("\"", "", trim($line[2]));
			$college = preg_replace("/'/", "\'", $college);
			if(preg_match("/Penn State/", $college)){
				$college = "Pennsylvania State University";
			}
			
			$select = "SELECT id from players where first_name='$first' and last_name='$last'";
			echo "$select<br>";
			$result = mysql_query($select);
			if($result && mysql_num_rows($result) != 1){
				$select = "SELECT id from players where first_name like '$first' and last_name like '$last' and college like '%$college%'";
				echo "$select<br>";
				$result = mysql_query($select);
				if(mysql_num_rows($result) == 0 && $middle != null){
					$l = preg_replace("/([A-Z])/e", "strtolower('\\1')", $last);
					$select = "SELECT id from players where first_name='$first' and last_name='$middle $l' and college like '%$college%'";
					echo "$select<br>";
					$result = mysql_query($select);
					if(mysql_num_rows($result) == 0){
						$m = preg_replace("/([A-Z])/e", "strtolower('\\1')", $middle);
						$select = "SELECT id from players where first_name='$first $m' and last_name='$last' and college like '%$college%'";
						echo "$select<br>";
						$result = mysql_query($select);
						if(mysql_num_rows($result) == 0 && $nickname != null){
							$select = "SELECT id from players where nickname='$first' and last_name='$last'";
							echo "$select<br>";
							$result = mysql_query($select);
						}
					}
				}
			}
			$r = mysql_fetch_assoc($result);
			$id = $r['id'];
			if(mysql_num_rows($result) < 1){
				echo "<span style='color:red;'>NEW $first $last $college</span> <br>";
				$player_insert = "	INSERT into players (first_name, last_name, $teamOrCollege)
									VALUES ('$first', '$last', '$college')";
				echo "<span style='color:red;'>Player insert</span>:<br>$player_insert <br>";
				mysql_query($player_insert);
				$id = mysql_insert_id();
				echo "<span style='color:blue;'>Insert new Award $title</span><br>";
				$query = "	INSERT into awards (award_title, player_id, year, type) 
						VALUES ('$title', $id, $year, '$type')";
				echo "<span style='color:blue;'>Award insert</span>:<br> $query<br><br>";
				mysql_query($query);
				continue;
				
			}
			else{
				echo "<span style='color:purple;'>Player already exists</span><br>";
			}						
				
			if(!$id){//this shouldn't happen...
				continue;
			}
			//check if award is already there
			$award_select = "Select * from awards where award_title = '$title' and year = $year and player_id = $id and type='$type'";
			$result = mysql_query($award_select);
			$r = mysql_fetch_assoc($result);
			$a_id = $r['id'];
			if($r && mysql_num_rows($r) > 0 && $a_id != $id){
				//the award returned an actual id, so it exists
				echo "<span style='color:red;'>Award Exists</span> <br>";
				continue;
			}
			echo "<span style='color:blue;'>Insert new Award $title</span><br>";
			$query = "	INSERT into awards (award_title, player_id, year, type) 
						VALUES ('$title', $id, $year, '$type')";
			echo "<span style='color:blue;'>Award insert</span>:<br> $query<br><br>";
			$result = mysql_query($query);
			echo "<br>";
		}
		if (!feof($handle)) {
			echo "Error: unexpected fgets() fail\n";
		}
		fclose($handle);
	}
	else{
		echo var_dump($php_errormsg);
		echo "failed to read file";
	}
}

?>